package com.pacifictextiles.qc.inspection.server.model.dao.impl;
import java.util.LinkedList;
import java.util.List;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import com.pacifictextiles.qc.inspection.server.model.dao.IWasteDAO;
import com.pacifictextiles.qc.inspection.server.model.dbc.DatabaseConnection;
import com.pacifictextiles.qc.inspection.server.model.vo.Waste;

public class WasteDAOImpl implements IWasteDAO {

	@Override
	public boolean addWaste(Waste waste) throws Exception {

		DatabaseConnection dbc = new DatabaseConnection();
		Connection conn = dbc.getConnection();
		PreparedStatement pStatement = null;
		boolean isSuccess = false;

		String insert = "INSERT INTO  `qc_inspection`.`waste` "
				+ "(`indexInRoll` ,`rollNo` ,`beginPosition` ,`endposition` ,"
				+ "`wasteLength` ,`defectCode` ,`dateAndTime`) " + "VALUES "
				+ "(?,  ?,  ?,  ?,  " + "?,  ?, CURRENT_TIMESTAMP);";

		try {

			// 准备好预处理查询语句
			pStatement = conn.prepareStatement(insert);

			// 赋值到查询语句中？号的位置从1到N
			pStatement.setInt(1, waste.getIndexInRoll());
			pStatement.setString(2, waste.getRollNo());
			pStatement.setFloat(3, waste.getBeginPosition());
			pStatement.setFloat(4, waste.getEndPosition());
			pStatement.setFloat(5, waste.getWasteLength());
			pStatement.setInt(6, waste.getDefectCode());

			// 执行预处理语句 用 validate 接受返回的 int 判断是否执行成功
			int validate = 0;
			System.out.print("The validation num is:" + validate + "\n");
			validate = pStatement.executeUpdate();
			System.out.print("The validation num is:" + validate + "\n"
					+ "执行通过" + "\n");

			// 判断是否执行成功
			if (validate > 0) {
				System.out.print("WasteDaoImpl:  添加waste成功" + "\n\n");
			}

			// 关闭数据库连接
			dbc.close();

			// 返回 boolean 值判断是否执行成功
			return isSuccess;

		} catch (SQLException e) {
			// e.printStackTrace();
			System.out.print("WasteDaoImpl:  请检查插入语句是否正确" + "\n\n");
		}

		// 返回 boolean 值判断是否执行成功
		return isSuccess;
	}

	@Override
	public boolean deleteWaste(String rollNo, int indexInRoll) throws Exception {

		DatabaseConnection dbc = new DatabaseConnection();
		Connection conn = dbc.getConnection();
		PreparedStatement pStatement = null;
		boolean isSuccess = false;

		String delete = "DELETE FROM `qc_inspection`.`waste` "
				+ "WHERE `waste`.`rollNo` = ?"
				+ "AND `waste`.`indexInRoll` = ?";
		try {

			// 准备好预处理查询语句
			pStatement = conn.prepareStatement(delete);

			// 赋值到查询语句中？号的位置从1到N
			pStatement.setString(1, rollNo);
			pStatement.setInt(2, indexInRoll);

			// 执行预处理语句 用 validate 接受返回的 int
			int validate = 0;
			System.out.print("The validation num is:" + validate + "\n");
			validate = pStatement.executeUpdate();
			System.out.print("The validation num is:" + validate + "\n"
					+ "执行通过" + "\n");

			// 判断是否执行成功
			if (validate > 0) {
				System.out.print("WasteDaoImpl:  删除waste成功" + "\n\n");
				isSuccess = true;
			} else {
				System.out.print("WasteDaoImpl:  找不到这一个waste" + "\n\n");
			}

			// 关闭数据库连接
			dbc.close();

			// 返回 boolean 值判断是否执行成功
			return isSuccess;

		} catch (SQLException e) {
			// e.printStackTrace();
			System.out.print("WasteDaoImpl:  请检查删除语句是否正确" + "\n");
		}

		// 返回 boolean 值判断是否执行成功
		return isSuccess;
	}

	@Override
	public Waste modifyWaste(String rollNo, int indexInRoll) {
		// TODO Auto-generated method stub
		return null;
	}

	@Override
	public List<Waste> getWasteList(String rollNo) throws Exception {

		DatabaseConnection dbc = new DatabaseConnection();
		Connection conn = dbc.getConnection();
		PreparedStatement pStatement = null;
		ResultSet rs = null;
		List<Waste> wasteList = new LinkedList<Waste>();

		// select预处理语句中，使用?代替变量会报错
		String select = "SELECT *  FROM `waste` WHERE `rollNo` = '"+rollNo+"'";

		try {

			// 准备好预处理查询语句
			pStatement = conn.prepareStatement(select);

			// 执行预处理语句
			rs = pStatement.executeQuery(select);

			if (rs != null) {

				// 相当于has next 无下一个return false
				while (rs.next()) {
					Waste waste = new Waste();

					waste.setIndexInRoll(rs.getInt("indexInRoll"));
					waste.setRollNo(rs.getString("rollNo"));
					waste.setBeginPosition(rs.getFloat("beginPosition"));
					waste.setEndPosition(rs.getFloat("endposition"));
					waste.setWasteLength(rs.getFloat("wasteLength"));
					waste.setDefectCode(rs.getInt("defectCode"));
					waste.setDateAndTime(rs.getString("dateAndTime"));

					wasteList.add(waste);
				}

				// 顺利执行后输出提示
				System.out.print("WasteDaoImpl:  获取链表成功\n");
				dbc.close();
				
				//return 后不执行后面的语句
				return wasteList;
			}
		} catch (Exception e) {

			// 抓到exception 给出提示
			//e.printStackTrace();
			System.out.print("WasteDaoImpl:  获取链表失败\n");
		}

		// result set 返回值为空，给出提示
		System.out.print("WasteDaoImpl:  执行查询成功哦，但是查不到结果");
		return null;
	}

}
